package cn.jdy.audit.service;

import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.jdy.audit.entity.ForumRecord;
import cn.jdy.framework.db.hibernate.BaseDao;
import cn.jdy.framework.log.BaseLog;
import cn.jdy.framework.util.DateUtil;
import cn.jdy.framework.util.StringUtil;
import cn.jdy.platform.service.ConfService;

/**
 * 数据分析论坛记录数据
 * 
 * @author yym
 * @since 110116
 */
@Service
public class ForumRecordService extends BaseDao<ForumRecord> {

	@Autowired
	private ConfService confService;

	/**
	 * 按条件查询
	 * 
	 * @param fr
	 * @return
	 */
	public List<ForumRecord> query(ForumRecord fr, String... date) {
		if (fr == null)
			return super.findAll();
		if (fr.getiDisplayLength() > 0) {
			return super.find(createFilter(fr, date), fr.getiDisplayStart(),
					fr.getiDisplayLength());
		}
		return super.find(createFilter(fr, date));
	}

	/**
	 * 查询结果总数
	 * 
	 * @param fr
	 * @return
	 */
	public Long count(ForumRecord fr, String... date) {
		return super.count(createFilter(fr, date));
	}

	/**
	 * 拼装条件
	 * 
	 * @param fr
	 * @return
	 */
	private DetachedCriteria createFilter(ForumRecord fr, String... date) {
		DetachedCriteria dc = DetachedCriteria.forClass(entityClass);
		if (date.length > 1 && !StringUtil.isNullOrEmpty(date[0])
				&& !StringUtil.isNullOrEmpty(date[1])) {
			dc.add(Restrictions.between("add_time",
					DateUtil.parseDate(date[0]), DateUtil.parseDate(date[1])));
		}
		if (!StringUtil.isNullOrEmpty(fr.getUser_ip())) {
			dc.add(Restrictions.like("user_ip", fr.getUser_ip()));
		}
		if (!StringUtil.isNullOrEmpty(fr.getUser_account())) {
			dc.add(Restrictions.like("user_account", fr.getUser_account()));
		}
		if (!StringUtil.isNullOrEmpty(fr.getForum_title())) {
			dc.add(Restrictions.like("forum_title", fr.getForum_title()));
		}
		if (!StringUtil.isNullOrEmpty(fr.getForum_type())) {
			dc.add(Restrictions.eq("forum_type", fr.getForum_type()));
		}
		dc.addOrder(Order.desc("createTime"));
		return dc;
	}

	/**
	 * cnt=2 查询不同IP数据集,结果仅含出现次数最多的前limitSize的 字段值,默认按升序排列
	 * 
	 * @param limitSize
	 * @return
	 */
	public List<String> queryTopIPs(int limitSize) {
		String sql = "select fr.user_ip from forum_record fr GROUP BY fr.user_ip order by fr.user_ip limit "
				+ limitSize;
		SQLQuery sqlQuery = em().createSQLQuery(sql);
		return sqlQuery.list();
	}

	/**
	 * 查询不同IP数据集,结果仅含出现次数最多的前limitSize的次数
	 * 
	 * @param limitSize
	 * @return
	 */
	public List<Integer> queryTopIPsNum(int limitSize) {
		String sql = "select count(fr.user_ip) from forum_record fr GROUP BY fr.user_ip order by fr.user_ip limit "
				+ limitSize;
		SQLQuery sqlQuery = em().createSQLQuery(sql);
		return sqlQuery.list();
	}

	/**
	 * cnt=1 查询对应ip的访问论坛类型前limitSize的类型
	 * 
	 * @param ipAddr
	 * @param limitSize
	 * @return
	 */
	public List<Integer> queryTopForumtypesByTopIp(String ipAddr, int limitSize) {
		String sql = "select fr.forum_type from forum_record fr WHERE fr.user_ip = '";
		sql += ipAddr
				+ "' GROUP BY fr.forum_type order by count(fr.forum_type) DESC limit "
				+ limitSize;
		SQLQuery sqlQuery = em().createSQLQuery(sql);
		return sqlQuery.list();
	}

	/**
	 * 查询对应ip的访问论坛类型前limitSize的类型数
	 * 
	 * @param ipAddr
	 * @param limitSize
	 * @return
	 */
	public List<Integer> queryTopForumtypesByTopIpNum(String ipAddr,
			int limitSize) {
		String sql = "select count(fr.forum_type) from forum_record fr WHERE fr.user_ip = '";
		sql += ipAddr
				+ "' GROUP BY fr.forum_type order by count(fr.forum_type) DESC limit "
				+ limitSize;
		SQLQuery sqlQuery = em().createSQLQuery(sql);
		return sqlQuery.list();
	}

	/**
	 * cnt=4/3 查询指定Ip在指定时间段访问论坛的所有时间点
	 * 
	 * @param ipAddr
	 * @param fromD
	 * @param toD
	 * @return
	 */
	public List<Integer> queryTopIpsVisitNumFromTo(String ipAddr, String fromD,
			String toD) {
		Integer fd = DateUtil.parseDate(fromD);
		Integer td = DateUtil.parseDate(toD);
		String sql = "select fr.add_time from forum_record fr WHERE fr.user_ip = '"
				+ ipAddr + "'";
		if (fd < td) {
			sql += " AND fr.add_time BETWEEN ";
			sql += fd + " AND " + td;
		} else if (fd == td) {
			sql += " AND fr.add_time=" + fd;
		}
		sql += " GROUP BY fr.add_time order by fr.add_time ";
		SQLQuery sqlQuery = em().createSQLQuery(sql);
		return sqlQuery.list();
	}

	/**
	 * cnt5/4 查询指定Ip在指定时间段访问论坛的次数
	 * 
	 * @param ipAddr
	 * @param fromD
	 * @param toD
	 * @return
	 */
	public List<Integer> queryTopIpsVisitNumFromToNum(String ipAddr,
			String fromD, String toD) {
		Integer fd = DateUtil.parseDate(fromD);
		Integer td = DateUtil.parseDate(toD);
		String sql = "select count(fr.id) from forum_record fr WHERE fr.user_ip = '"
				+ ipAddr + "'";
		if (fd < td) {
			sql += " AND fr.add_time BETWEEN ";
			sql += fd + " AND " + td;
		} else if (fd == td) {
			sql += " AND fr.add_time=" + fd;
		}
		sql += " GROUP BY fr.add_time order by fr.add_time ";
		SQLQuery sqlQuery = em().createSQLQuery(sql);
		return sqlQuery.list();
	}

	/**
	 * 查询某个时间点的某ip访问量 时间段
	 * 
	 * @param ipAddr
	 * @param date
	 * @return
	 */
	public List<Integer> queryTopIpsVisitNum(String ipAddr, long fromDate,
			long toDate) {
		String sql = "select count(fr.id) from forum_record fr WHERE fr.user_ip = '"
				+ ipAddr + "'";
		if (fromDate < toDate) {
			sql += " AND fr.add_time BETWEEN ";
			sql += fromDate + " AND " + toDate;
		} else if (fromDate == toDate) {
			sql += " AND fr.add_time=" + fromDate;
		}
		sql += " GROUP BY fr.add_time order by fr.add_time ";
		SQLQuery sqlQuery = em().createSQLQuery(sql);
		List<Integer> list = sqlQuery.list();
		if (list.size() == 0) {
			list.add(0, 1);
			return list;
		}
		return list;
	}
	
	/**
	 * 列表头部的图表展示所有访问次数
	 * @param user_ip
	 * @param user_account
	 * @param forum_title
	 * @param fromD
	 * @param toD
	 * @param minutes
	 * @param type
	 * @return
	 */
	public List<Map> queryTotals(String user_ip, String user_account, String forum_title,
			String fromD, String toD, int minutes, int type) {
		String sql="";
		try {
			if((fromD!="")&&(minutes!=0)){
				 sql = "select count(1) aa1,DATE_ADD(date_format('"+fromD+"','%Y-%m-%d %H:%i:%s'),INTERVAL "+minutes+"*("+type+"-1) MINUTE) time1"
				 		+ " from forum_record where unix_timestamp(DATE_ADD(date_format('"+fromD+"','%Y-%m-%d %H:%i:%s'),INTERVAL "+minutes+"*("+type+"-1) MINUTE))<add_time "
				 		+ "and unix_timestamp(DATE_ADD(date_format('"+fromD+"','%Y-%m-%d %H:%i:%s'),INTERVAL "+minutes+"*"+type+" MINUTE)) >add_time";				 
			}else{
				sql = "select count(1) aa1,DATE_ADD(date_format(SUBSTR(now(),1,10),'%Y-%m-%d'),INTERVAL (60*4*("+type+"-1)) MINUTE) time1"
						+ " from forum_record where unix_timestamp(DATE_ADD(date_format(SUBSTR(now(),1,10),'%Y-%m-%d'),INTERVAL (60*4*("+type+"-1)) MINUTE))<add_time "
				 		+ "and unix_timestamp(DATE_ADD(date_format(SUBSTR(now(),1,10),'%Y-%m-%d'),INTERVAL (60*4*("+type+")) MINUTE)) >add_time";
			}	
			if(user_ip!=""){
				 sql += " and user_ip = '"+user_ip+"' ";
			 }
			 if(user_account!=""){
				 sql += " and user_account like '%"+user_account+"%' ";
			 }
			 if(forum_title!=""){
				 sql += " and forum_title like '%"+forum_title+"%' ";
			 }
			//System.out.println(sql+"11111111");
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			return sqlQuery.list();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "qq查询异常！", e);
		}
		return null;
	}
	/**
	 * no 查询不同IP数据集,结果仅含IP值
	 * 
	 * @param fr
	 * @return
	 */
	// public List<ForumRecord> queryIPs(ForumRecord fr){
	// List<ForumRecord> frList=new ArrayList<ForumRecord>();
	// String sql = "select DISTINCTROW fr.user_ip from forum_record fr";
	// SQLQuery sqlQuery = em().createSQLQuery(sql);
	// List<String> ipList = sqlQuery.list();
	// for(int i=0;i<ipList.size();i++){
	// ForumRecord temp = new ForumRecord();
	// temp.setUser_ip(ipList.get(i));
	// frList.add(i, temp);
	// }
	// return frList;
	// }
	/**
	 * no 根据指定IP： 查询：论坛类型，结果仅含forum_type值
	 * 
	 * @param fr
	 * @return
	 */
	// public List<ForumRecord> queryTypesByIP(ForumRecord fr){
	// List<ForumRecord> frList=new ArrayList<ForumRecord>();
	// String sql =
	// "select DISTINCTROW fr.forum_type from forum_record fr where user_ip="+fr.getUser_ip();
	// SQLQuery sqlQuery = em().createSQLQuery(sql);
	// List<Integer> typeList = sqlQuery.list();
	// for(int i=0;i<typeList.size();i++){
	// ForumRecord temp = new ForumRecord();
	// temp.setForum_type(typeList.get(i));
	// frList.add(i, temp);
	// }
	// return frList;
	// }

	/**
	 * no 根据指定IP： 查询：账户，结果仅含user_account值
	 * 
	 * @param fr
	 * @return
	 */
	// public List<ForumRecord> queryAcntByIP(ForumRecord fr){
	// List<ForumRecord> frList=new ArrayList<ForumRecord>();
	// String sql =
	// "select DISTINCTROW fr.user_account from forum_record fr where user_ip="+fr.getUser_ip();
	// SQLQuery sqlQuery = em().createSQLQuery(sql);
	// List<String> typeList = sqlQuery.list();
	// for(int i=0;i<typeList.size();i++){
	// ForumRecord temp = new ForumRecord();
	// temp.setUser_account(typeList.get(i));
	// frList.add(i, temp);
	// }
	// return frList;
	// }
}
